from app.utils.mysql_util import get_connection

def insert_stockout_order(stockout_data):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        sql = """INSERT INTO stockout_order (
            stockout_no, warehouse_no, order_type_no, status,
            consignee, province, city, district, address,
            mobile, telno, logistics_no, logistics_name,
            express_no, created, modified
        ) VALUES (
            %(stockout_no)s, %(warehouse_no)s, %(order_type_no)s, %(status)s,
            %(consignee)s, %(province)s, %(city)s, %(district)s, %(address)s,
            %(mobile)s, %(telno)s, %(logistics_no)s, %(logistics_name)s,
            %(express_no)s, %(created)s, %(modified)s
        ) ON DUPLICATE KEY UPDATE
            status = VALUES(status),
            logistics_no = VALUES(logistics_no),
            logistics_name = VALUES(logistics_name),
            modified = VALUES(modified)
        """
        cursor.execute(sql, stockout_data)
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

def insert_stockout_detail(detail_data):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        sql = """INSERT INTO stockout_detail (
            stockout_no, goods_no, spec_no, goods_name,
            spec_name, num, price, total_amount,
            position_no, batch_no, created, modified
        ) VALUES (
            %(stockout_no)s, %(goods_no)s, %(spec_no)s, %(goods_name)s,
            %(spec_name)s, %(num)s, %(price)s, %(total_amount)s,
            %(position_no)s, %(batch_no)s, %(created)s, %(modified)s
        ) ON DUPLICATE KEY UPDATE
            num = VALUES(num),
            price = VALUES(price),
            total_amount = VALUES(total_amount),
            modified = VALUES(modified)
        """
        cursor.execute(sql, detail_data)
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

def insert_stock_change(stock_change_data):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        sql = """INSERT INTO stock_change (
            change_id, goods_id, spec_id, warehouse_no, change_type,
            change_num, change_time, order_no, remark
        ) VALUES (
            %(change_id)s, %(goods_id)s, %(spec_id)s, %(warehouse_no)s, %(change_type)s,
            %(change_num)s, %(change_time)s, %(order_no)s, %(remark)s
        ) ON DUPLICATE KEY UPDATE
            change_num = VALUES(change_num),
            change_time = VALUES(change_time),
            remark = VALUES(remark)
        """
        cursor.execute(sql, stock_change_data)
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close() 